﻿using AnlianCommon.Common.Domains.ProductionManagement;
using AnlianCommon.Common.Domains.UserPermissions;
using AnlianCommon.Common.DTO;
using AnlianCommon.Common.Infrastructure.IRepository;
using MediatR;
using Microsoft.EntityFrameworkCore;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Globalization;

namespace AnlianCommon.API.Commands.Personnels
{
    public class PersonnelsImportHandler : IRequestHandler<PersonnelsImportCommand, Result>
    {
        private readonly IBaseRepository<Personnel> _personnelRepository;

        public PersonnelsImportHandler(IBaseRepository<Personnel> personnelRepository)
        {
            _personnelRepository = personnelRepository;
        }
        /// <summary>
        /// 导入人员信息
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task<Result> Handle(PersonnelsImportCommand request, CancellationToken cancellationToken)
        {
            var result = new Result();
            try
            {
                var ext = Path.GetExtension(request.File.FileName);
                if (ext == ".xls" || ext == ".xlsx")
                {

                }
                else
                {
                    result.Msg = "您上传的文件类型不正确";
                    return result;
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    request.File.CopyTo(ms);
                    ms.Position = 0;
                    //根据文件扩展名创建对应的工作簿对象(HSSFWorkbook用于.xls，XSSFWorkbook用于.xlsx)
                    //获取工作簿
                    IWorkbook wb = null;
                    //创建97-2003
                    if (ext == ".xls")
                    {
                        wb = new HSSFWorkbook(ms);
                    }
                    //创建2007及以上版本
                    else
                    {
                        wb = new XSSFWorkbook(ms);
                    }
                    //获取sheet 获取工作簿中的第一个工作表
                    ISheet sheet = wb.GetSheetAt(0);
                    int totalRows = sheet.LastRowNum;//总行数
                    int totalCells = sheet.GetRow(0).LastCellNum;//总列数
                    // 创建一个用于存储解析后的数据的列表
                    var personnelList = new List<Personnel>();
                    //循环总行数 开头必须是1 不然从表头开始
                    //遍历工作表的每一行（从第二行开始，因为第一行通常是表头）
                    for (int i = 1; i <= totalRows; i++)
                    {
                        //获取当前行
                        IRow row = sheet.GetRow(i);
                        //这个类必须写到循环里边 不然你批量添加只能添加一行
                        var dto = new Personnel();// 在循环内创建新的对象实例
                        //下标从0开始 导入时 外键需进行判断 客户不知道你是外键 只会输入文字 需要在后台判断
                        var PersName = row.GetCell(0).ToString();
                        var basic = await (await _personnelRepository.GetAll()).Where(c => c.PersName== PersName).FirstOrDefaultAsync();
                        dto.PersId = basic.PersId;
                        dto.PersSex = row.GetCell(1).ToString();
                        dto.PersCardType = row.GetCell(2).ToString();
                        dto.PersCard = row.GetCell(3).ToString();
                        dto.PersPhone = row.GetCell(4).ToString();
                        dto.PersEmail = row.GetCell(5).ToString();
                        dto.PersCollege = row.GetCell(6).ToString();
                        dto.Education = row.GetCell(7).ToString();
                        dto.EnterpriseId =Convert.ToInt32(row.GetCell(8).ToString()) ;
                        dto.StaffState = row.GetCell(9).ToString();
                        dto.DepartmentId = Convert.ToInt32(row.GetCell(10).ToString()) ;
                        //日期转换
                        //日期在excel表中必须是年月日格式 但不能加时分秒 (注意!!!!)
                        string persEntryDate = row.GetCell(11).ToString();
                        string[] persEntryDates = { "dd-M月-yyyy", "yyyy-M-dd" };
                        DateTime parsEntryDatees;
                        foreach (var format in persEntryDates)
                        {
                            if (DateTime.TryParseExact(persEntryDate, new[] { format }, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsEntryDatees))
                            {
                                dto.PersEntry = Convert.ToDateTime(parsEntryDatees.ToString("yyyy-MM-dd"));
                                break;//成功解析后跳出循环
                            }
                        }
                        //dto.PersEntry = row.GetCell(12).ToString();
                        //日期转换
                        //日期在excel表中必须是年月日格式 但不能加时分秒 (注意!!!!)
                        string PersRegularDate = row.GetCell(12).ToString();
                        string[] PersRegularDates = { "dd-M月-yyyy", "yyyy-M-dd" };
                        DateTime parsedDates;
                        foreach (var format in PersRegularDates)
                        {
                            if (DateTime.TryParseExact(PersRegularDate, new[] { format }, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsedDates))
                            {
                                dto.PersRegular = Convert.ToDateTime(parsedDates.ToString("yyyy-MM-dd"));
                                break;//成功解析后跳出循环
                            }
                        }
                        //dto.PersRegular = row.GetCell(13).ToString();
                        dto.PersFunction = row.GetCell(13).ToString();
                        dto.PresTrain = row.GetCell(14).ToString();
                        dto.PresLevel = row.GetCell(15).ToString();
                        dto.PresAccount = row.GetCell(16).ToString();
                        dto.PresPass = row.GetCell(17).ToString();
                        dto.RoleId =Convert.ToInt32(row.GetCell(18).ToString());
                        personnelList.Add(dto);
                    }
                    var res =await _personnelRepository.AddRange(personnelList);
                    if (res>0) 
                    {
                        result.Msg = "导入成功";
                    }
                    else
                    {
                        result.Msg = "导入失败";
                    }
                }

            }
            catch (Exception ex)
            {
                result.Msg ="导入异常"+ex.Message;
                
            }
            return result;
        }
    }
}
